Database schema upgrade

Database schema upgrade overview

Initial database schema creation

As you already know DataObjects.Net automatically generates database schema when you build a domain. You don’t need to create tables and columns manually, everything you need to do is just to create empty database and specify appropriate connection string in domain configuration.

Let us suppose we have created our model consisting of one class:

[HierarchyRoot]
public class Order : Entity
{
  [Key, Field]
  public int Id { get; private set;}

  [Field]
  public string ProductName { get; set; }

  [Field]
  public int Quantity { get; set; }
}

Then build domain:

var domainConfig = new DomainConfiguration("sqlserver://localhost/myDatabase");
domainConfig.Types.Register(myAssembly, "MyProduct.Model");
domainConfig.UpgradeMode = DomainUpgradeMode.Recreate;
var domain = await Domain.BuildAsync(domainConfig);

When domain is built, database structure will look like this:

You can see that there is Order table corresponding to Order entity in our model and several additional tables.

Existing database schema upgrade

You can also notice that in previous example we specified DomainUpgradeMode.Recreate as upgrade mode in domain configuration, it means that existing database schema and data should be completely cleared before new schema is generated. This mode is very useful when you want to create new database or perform unit tests, but when our application is in use we need to upgrade database schema from one version to another without loosing existing data, moreover sometimes existing data should be also modified, e.g. we may want to change some column type or move some data from one class to another.

There are several available values of DomainUpgradeMode :

Validate – Domain will check whether database schema is compatible with persistent model, anyway it will not be modified. If schema is incompatible, exception will be thrown.
Recreate – Database schema will be completely recreated, all existing data will be removed.
Perform – Storage upgrade will be performed. All missing database structures (columns, tables) will be added, excess ones will be removed.
PerformSafely (default value) – The same as Perform mode, but any actions leading to data or precision lost are never performed to the database unless all of them are explicitly enabled.

Automatic upgrade

In order to upgrade exiting database schema and save stored data we can use Perform or PerformSafely mode. In simple cases such upgrade can be performed fully automatically.

For example let’s create several orders and then change our model: Add Customer class and Customer property to Order class:

[HierarchyRoot]
public class Order : Entity
{
  // ...

  [Field]
  public Customer Customer { get; set;}
}

[HierarchyRoot]
public class Customer : Entity
{
  [Key, Field]
  public int Id { get; private set; }

  [Field]
  public string Name { get; set; }
}

If we build our domain with Validate mode, we’ll get SchemaSynchronizationException in Build() method. If we build it with Recreate mode, it will create new database schema, but data will be lost. When we have our domain built with Perform or PerformSafely upgrade mode, we’ll see following database structure:

DataObjects.Net automatically added Customer table and Customer.Id column to Order table. New table Customer is empty and value of new field is null, but all existing orders are saved.

Upgrade handlers and hints

Automatic upgrade works perfectly when we add new classes and properties to our model, but sometimes we need to perform some specific schema modifications, for example we can rename some properties or classes.

Rename hints

Let’s rename Customer class to Person and, for example, Name property to FullName.

[HierarchyRoot]
public class Order : Entity
{
  // ...

  [Field]
  public Person Customer { get; set;}
}

[HierarchyRoot]
public class Person : Entity
{
  [Key, Field]
  public int Id { get; private set; }

  [Field]
  public string FullName { get; set; }
}

To upgrade schema correctly, we should inform domain that Person class is renamed Customer, otherwise it will remove Customer table with all records and create new empty Person table. To do this we use so called upgrade handlers and upgrade hints.

Upgrade handler is a class responsible for upgrade of persistent classes from one assembly. It must be inherited from UpgradeHandler or implement IUpgradeHandler interface, it also must have a parameterless constructor. Each assembly with persistent model should contain exactly one upgrade handler class, it will be automatically found when schema is being upgraded. UpgradeHandler class contains a set of methods and properties that can be overridden for customizing schema upgrade process.

public class Upgrader : UpgradeHandler
{
  protected override void AddUpgradeHints(Xtensive.Collections.ISet<UpgradeHint> hints)
  {
    hints.Add(new RenameTypeHint("MyProduct.Model.Customer", typeof (Person)));
    hints.Add(new RenameFieldHint(typeof (Person), "Name", "FullName"));
  }
}

In our example we have overriden AddUpgradeHints method to add two hints. If we build domain with new model it will find our upgrade handler and will rename columns and tables instead of removing and creating new ones, so their data will be saved.

RenameFieldHint describes a renaming of a persistent field. You should create this hint when you rename a persistent field.
RenameTypeHint describes a renaming of a persistent type. You should create this hint when you rename entities or structures.

Remove hints

In order to understand meaning of remove hints let’s remember difference between Perform and PerformSafely upgrade modes. Second one requires all modifications leading to losing data to be explicitly permitted. For example, if we remove some persistent field or persistent class from our model, domain builder will remove appropriate database structure in Perform mode and will throw exception in PerformSafely mode. To perform such operations in safe mode we should use following hints:

RemoveFieldHint describes a renaming of a persistent field.
RemoveTypeHint describes a renaming of a persistent type.

For example, if you want to remove Quantity field from Order in PerformSafely mode, you would remove appropriate property from Order class and add RemoveFieldHint to hints collection in AddUpgradeHints method.

Change type hint

ChangeFieldTypeHint is used when you want to change type of some persistent column. For example you have Order class with Number property of int type, but new version of application should allow to store numbers with literal prefix, and we definitely want to change column type to string and convert all stored numbers. To do this we change type of property to string:

[Field(Length = 20)]
public string Number { get; set; }

And add ChangeFieldTypeHint in our custom upgrade handler:

hintSet.Add(new ChangeFieldTypeHint(typeof(Order), "Number"));

If hint is not added domain builder will create new column without saving data. In safe mode an exception will be thrown.

You should also use ChangeFieldTypeHint hint if you use PerformSafely mode change length of string field or precision of decimal field.

When column type is changed domain builder uses standard functions of RDBMS to convert values. In our example we changed int type to string and values were successfully converted. But if types are incompatible in particular RDBMS, we’ll get appropriate exception.

Move and copy field hints

Imagine we have Order type inherited from Document, Order class has Number property. We want number to be a property of Document class. We can declare such property in Document and remove it from Order, but we also have to copy data from Order table to Document table before order’s column will be removed.

To do this we can use MoveFieldHint, that moves some field data to another class within single hierarchy:

hintSet.Add(new MoveFieldHint("MyProduct.Model.Order", "Number", typeof(Document)));

Whereas MoveFieldHint implies that original field is removed, CopyFieldHint don’t, it just copies. To implement our upgrade handler using CopyFieldHint we should add RemoveFieldHint.

hintSet.Add(new CopyFieldHint("Order", "Number", typeof(Order)));
hintSet.Add(new RemoveFieldHint("Order", "Number"));

Recycled types and fields

There are some cases where automatic upgrade with upgrade hints is not enough to describe changes in our model. For example suppose we decided to extract product information from Order class to separate Product class. In original model we have Order.ProductName property of string type. In result model we have Order.Product property of Product type, which has Name property.

So we should enumerate all orders during upgrade process, create new product for each unique product name, and assign newly created products to Product property of each order. We can not do this when domain already built because column ProductName should be removed from Order table as a result of schema upgrade. Since it is rather complex operation, we will perform it manually.

First of all we should make necessary changes to our model – add new persistent class and persistent field. Then we mark old field ProductName as recycled, it is also recommended to mark it as obsolete:

[HierarchyRoot]
public class Order : Entity
{
  // ...

  [Field]
  public Product Product { get; set; }

  [Field, Obsolete, Recycled]
  public string ProductName { get; set; }
}

[HierarchyRoot]
public class Product : Entity
{
  [Key, Field]
  public int Id { get; private set; }

  [Field]
  public string Name { get; set; }
}

Next thing we should do is to implement our custom upgrade handler for this upgrade:

public class Upgrader : UpgradeHandler
{
  public override void OnUpgrade()
  {
    var session = Session.Demand();
    foreach (var order in session.Query.All<Order>()) {
      var product = session.Query.All<Product>()
         .SingleOrDefault(p => p.Name==order.ProductName);
      if (product==null)
        product = new Product { Name = order.ProductName };
      order.Product = product;
    }
  }
}

As you can see, we have overridden OnUpgrade() method of upgrade handler and placed our custom logic there. We manually query the orders and create new products. When this method is executed, both old and new fields are accessible. But when domain is already built, you can’t use recycled fields; moreover, their underlying tables and columns are removed at that moment.

In our example we used recycled fields, but it is also possible to use recycled persistent classes in exactly the same way. You need just mark types to be removed by [Recycled] attribute and recycle their data in OnUpgrade() method.

Getting even more control of upgrade

Sometimes upgrade requires even more management of process. For that UpgradeHandler provides a bunch of members which allow to do a lot during upgrade.

Properties contain settings for upgrade handler itself and also provide information from domain building process include:

IsEnabled - determines whether upgrade handler is enabled. Only one Enabled upgrade handler per assembly allowed.
Assembly - gets the assembly the handler is made for.
AssemblyName - gets the name of the assembly described by the handler.
AssemblyVersion - gets the version of the assembly described by this handler.
UpgradeContext - gets the upgrade context the handler is bound to. It contains different settings, services and other data that can be helpful during upgrade.
TypesMovementsAutoDetection - determines whether auto-detection of type movements between namespaces is enabled. By default it turned on. The mechanism helps to resolve conflicts when a persistent type is moved to another namespace but kept the meaning, no RenameTypeHint required. But if the meaning changed it would require DataObjects.Net to not resolve such conflicts. After tunring off auto-detection DataObjects.Net will rely on provided RenameTypeHints.

UpgradeHandler deliver different event-like methods that allow to perfrom user operations on different stages of Domain building. Synchronous and asynchronous variants execute when Domain.Build() and Domain.BuildAsync() are called respectively. By default asynchronous methods call their synchronous equivalent.

Following event-like methods which are available for override:

OnPrepare / OnPrepareAsync - override this method to perform actions before any operation on database is performed. Native connection to database is already provided in UpgradeContext so you are able to execute DbCommands if you will.
OnBeforeStage / OnBeforeStageAsync - override this method to perform actions before schemas are compared and synchronized. Note that database schema and metadata are already extracted here. Default implementation execute AddUpgradeHints and AddAutoHints during execution of this method on Upgrading stage. At this point new data become avalable in UpgradeContext, such as extracted information about previous domain model, type indentifiers it had, and full type map of current model (ExtractedDomainModel, ExtractedTypeMap and FullTypeMap). In this method you can define custom type identifiers for new persistent types by adding them to UpgradeContext.UserDefinedTypeMap. This map will take into account on generating type identifiers down the building process.
OnSchemaReady / OnSchemaReadyAsync - override this method to handle “at schema ready” event. The both extracted schema and target schema are ready at this moment as well as schema hints. Correspondance between types of extracted and target schemas is also available in UpgradeContext.UpgradedTypesMapping.
OnBeforeExecuteActions / OnBeforeExecuteActionsAsync - override this method to make correction to upgrade action sequence. All upgrade actions are figured out but not executed yet. Action sequence is provided as parameter. Difference of extracted and target schemas and upgrade actions become available in UpgradeContext
OnStage / OnStage - override this method to handle “at upgrade stage” event. All upgrade actions are already applied here. Session instance is provided and accessible via UpgradeContext property so querying data is possible.
OnUpgrade / OnUpgradeAsync - override this method to implement custom persistent data migration logic. The method executes only on Upgrading stage as part of OnStage execution.
OnComplete / OnCompleteAsync - override this method to perform any actions after all database operations are completed. The domain provided to this method is the instance that Domain.Build() / Domain.Build() will return.

Methods above are given in the order of their execution. Notice that such methods as OnPrepare, OnComplete and their asynchronous analogues execute only ones during Domain building despite domain upgrade mode; OnBeforeStage, OnSchemaReady, OnBeforeExecuteActions and OnStage, though, can execute twice if domain is building in Perform or PerfromSafely upgrade modes as these modes have

Upgrading and Final stages.

Additonal methods of UpgradeHandler available for override:

AddUpgradeHints - override this method to add upgrade hints to Upgrade.UpgradeContext.Hints collection.
AddRecycledDefinitions - override this method to add recycled definitions.
AddAutoHints - adds the “auto” hints - e.g. hints for recycled types. In vast majority of the cases you won’t need to override it but you are able to.
DetectAssemblyName - detects the name of the assembly this handler is made for. Method is used by AsseblyName property, override it if Assembly property is overridden to make assembly version be aligned with Assembly or override AsseblyName itself.
DetectAssemblyVersion - detects the version of the assembly this handler is made for. Method is used by AsseblyVersion property, override it if Assembly property is overridden to make assembly version be aligned with Assembly or override AsseblyVersion itself.

Legacy database schema support

DataObjects.Net can be used for mapping of entities to existing database. There are two special upgrade modes for this:

DomainUpgradeMode.LegacySkip skips any schema checks.
DomainUpgradeMode.LegacyValidate ensures all mapped structures (tables and columns) are available in database; fails with exception if something is absent.

Currently legacy mode has a set of limitations:

IDENTITY columns are not yet supported - you have to use generators or write your own ones.
Mapping to multiple schemas is not supported.
Mapping to views and stored procedures isn’t supported.
T4-based model code generation (ActiveRecord pattern) is not available, i.e. you should write the classes by your own.
If you’re going to use non-paired EntitySets, you can’t define table and column mappings for runtime-generated type that is used to represent its items. Usage of such non-paired EntitySets is optional, but this is pretty convenient to use them in many cases.
Also, you can’t fully control mappings for composite foreign keys.

Rules to follow:

Use DomainUpgradeMode.LegacySkip or DomainUpgradeMode.LegacyValidate to to turn this feature on.
If mapped table and column names differ from derived by default mapping rules, use [TableMapping] and [FieldMapping] attributes.
You must explicitly specify type discriminators (using [TypeDiscriminator] and [TypeDiscriminatorValue(...)] attributes), if you’re going to map hierarchies with inheritance. Note that inheritance hierarchies can also be represented as non-inherited types containing reference as key of “descendant” type.
Take all the above-mentioned limitations into account.

Here is an example of Legacy model mapping. Note that we are mapping 3 classes to the single table “Animals” and use ElementType property as type discriminator.

[HierarchyRoot(InheritanceSchema = InheritanceSchema.SingleTable)]
[TypeDiscriminatorValue("Animal", Default = true)]
[TableMapping("Animals")]
public class Animal : Entity
{
  [Field, Key]
  public Guid Id { get; private set; }

  [Field(Length = 50), TypeDiscriminator]
  [FieldMapping("Type")]
  public string ElementType { get; private set; }

  [Field(Length = 50)]
  public string Name { get; set; }

  [Field]
  public int Age { get; set; }

  [Field]
  [FieldMapping("Owner")]
  public Person Owner { get; set; }
}

[TypeDiscriminatorValue("Dog")]
public class Dog : Animal
{
}

[TypeDiscriminatorValue("Cat")]
public class Cat : Animal
{
}

Navigation

Database schema upgrade